package ApplicationsYoung;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import Connect.connector;

public class ApplicationsYoungDAO {
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public int idGet (String id){
		conn = connector.getConnection();
		int idnum = 0;
		try{
			
			String sql = "select `ID` from MEMBERS where Mem_Nick = ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				idnum = rs.getInt("ID");
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return idnum;
	}
	
	public int abliltySet (String[] certificate, int idnumber){
		conn = connector.getConnection();
		int sum = 0;
		try{
			
			String sql="insert into ABLILTY(Vvalue,Members_Id)values(?,?)";
			pstmt = conn.prepareStatement(sql);
			for(int i = 0 ; i<certificate.length ; i++){
				pstmt.setString(1,certificate[i]);				
				pstmt.setInt(2,idnumber);
				pstmt.executeUpdate();
				
				if(certificate[i].equals("保育教師")){
					sum = sum + 1;
				}else if(certificate[i].equals("看護婦")){
					sum = sum + 2;
				}else if(certificate[i].equals("医者")){
					sum = sum + 4;
				}else if(certificate[i].equals("幼稚園教師")){
					sum = sum + 8;
				}else if(certificate[i].equals("小学校教師")){
					sum = sum + 16;
				}else if(certificate[i].equals("講座卒業")){
					sum = sum + 32;
				}else{
					sum = sum + 64;
				}
			}

		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return sum;
	}
	
	public void abliltySet (String certificate, int idnumber){
		conn = connector.getConnection();
		try{
			
			String sql="insert into ABLILTY(Vvalue,Members_Id)values(?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,certificate);				
			pstmt.setInt(2,idnumber);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		
	}
	
	public int daySet2(String[] day, int idnumber){
		conn = connector.getConnection();
		int sum = 0;
		try{
			String sql ="insert into DAYCHECK(Dvalue,Members_Id)values(?,?)";
			
			pstmt = conn.prepareStatement(sql);
			
			for(int i = 0 ; i<day.length ; i++){
				pstmt.setString(1,day[i]);				
				pstmt.setInt(2,idnumber);
				pstmt.executeUpdate();
				
				if(day[i].equals("月")){
					sum = sum + 1;
				}else if(day[i].equals("火")){
					sum = sum + 2;
				}else if(day[i].equals("水")){
					sum = sum + 4;
				}else if(day[i].equals("木")){
					sum = sum + 8;
				}else if(day[i].equals("金")){
					sum = sum + 16;
				}else if(day[i].equals("土")){
					sum = sum + 32;
				}else if(day[i].equals("日")){
					sum = sum + 64;
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return sum;
	}
	
	public void daySet(String dayall, int idnumber){
		conn = connector.getConnection();
		try{
			String sql ="insert into DAYCHECK(Dvalue,Members_Id)values(?,?)";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1,dayall);				
			pstmt.setInt(2,idnumber);
			pstmt.executeUpdate();
		
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void adminYoungSet(String id, int idnumber){
		conn = connector.getConnection();
		try{
			String sql ="insert into ADMINYOUNG(ID,Mem_Id,Mem_Nick)values(NULL,?,?)";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,idnumber);
			pstmt.setString(2,id);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public String membersAddressGet(int idnumber){
		conn = connector.getConnection();
		String address = null;
		try{
			String sql ="select `Mem_Address1` from MEMBERS where ID = ?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,idnumber);
			rs = pstmt.executeQuery();
			while(rs.next()){
				address = rs.getString("Mem_Address1");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return address;
	}
	
	public void babyPlusSet(ApplicationsYoungBean youngbean){
		conn = connector.getConnection();
		try{
			String sql ="insert into BABYPLUS(Members_Id, Bby_Add, Bby_Kind, Bby_Gender, Bby_Contents, Bby_Photo, Bby_Money , Bby_Date, Bby_Check)values(?,?,?,?,?,?,?,Sysdate(),1)";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,youngbean.getMembers_Id());
			pstmt.setString(2,youngbean.getBby_Add());
			pstmt.setString(3,youngbean.getBby_Kind());
			pstmt.setInt(4,youngbean.getBby_Gender());
			pstmt.setString(5,youngbean.getBby_Countents());
			pstmt.setString(6,youngbean.getBby_Photo());
			pstmt.setString(7,youngbean.getBby_Money());
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public int babyPlusIdGet(int idnumber){
		conn = connector.getConnection();
		int bbpid = 0;
		try{
			String sql ="select `ID` from BABYPLUS where Members_Id = ?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,idnumber);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bbpid = rs.getInt("ID");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return bbpid;
	}
	
	public void babyPhotoSet(ApplicationsYoungBean youngbean){
		conn = connector.getConnection();
		try{
			String sql ="insert into BABYPHOTO(BabyPlus_Id, Bbp_NameK, Bbp_NameH, Bbp_Birth, Bbp_Gender, Bbp_Photo, Bbp_Members)values(?,?,?,?,?,?,?)";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,youngbean.getBabyplus_Id());
			pstmt.setString(2,youngbean.getBbp_NameK());
			pstmt.setString(3,youngbean.getBbp_NameH());
			pstmt.setDate(4,youngbean.getBbp_Birth());
			pstmt.setInt(5,youngbean.getBbp_Gender());
			pstmt.setString(6,youngbean.getBbp_Photo());
			pstmt.setInt(7,youngbean.getMembers_Id());
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public int babyPlusInquiry(int idnumber){
		conn = connector.getConnection();
		int num = 0;
		try{
			String sql ="select * from BABYPLUS where Members_ID = ?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,idnumber);
			rs = pstmt.executeQuery();
			while(rs.next()){
				num = rs.getInt("Bby_Check");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return num;
	}
	
	public void abliltySumSet (int idnumber, int ablilty){
		conn = connector.getConnection();
		try{
			
			String sql="insert into ABLILTYSUM(Members_Id,Vvalue_Sum)values(?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,idnumber);				
			pstmt.setInt(2,ablilty);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void daySumSet(int idnumber, int daysum){
		conn = connector.getConnection();
		try{
			
			String sql="insert into DAYCHECKSUM(Members_Id,Dvalue_Sum)values(?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,idnumber);				
			pstmt.setInt(2,daysum);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void cancelSet(int idnumber){
		conn = connector.getConnection();
		try{
			
			String sql="UPDATE BABYPLUS SET Bby_Check=2 WHERE Members_Id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,idnumber);				
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
}
